****
View
****

CREATE VIEW
===========

A view is a virtual table that does not exist physically. You can create a view by using an existing table or a query. **VIEW** and **VCLASS** are used interchangeably.

Use **CREATE VIEW** statement to create a view. For how to write view name, see :doc:`/sql/identifier`. 

::

    CREATE [OR REPLACE] {VIEW | VCLASS} <view_name>
                               [ <subclass_definition> ]
                               [ ( <view_column_def_comma_list> ) ]
                               [ CLASS ATTRIBUTE
                                 ( <column_definition_comma_list> ) ]
                               [ METHOD <method_definition_comma_list> ]
                               [ FILE <method_file_comma_list> ]
                               [ INHERIT <resolution_comma_list> ]
                               [ AS <select_statement> ]
                               [ WITH CHECK OPTION ]
     
    <view_column_definition> ::= <column_definition> | <column_name>
     
    <column_definition> :
    column_name column_type [ <default_or_shared> ] [ <column_constraint_list>]
     
    <default_or_shared> :
    {SHARED [ <value_specification> ] | DEFAULT <value_specification> } |
    AUTO_INCREMENT [ (seed, increment) ]
     
    <column_constraint> :
    NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY REFERENCES...
     
    <subclass_definition> :
    { UNDER | AS SUBCLASS OF } table_name_comma_list
     
    <method_definition> :
    [ CLASS ] method_name
    [ ( [ argument_type_comma_list ] ) ]
    [ result_type ]
    [ FUNCTION function_name ]
     
    <resolution> :
    [ CLASS ] { column_name | method_name } OF superclass_name
    [ AS alias ]

*   **OR REPLACE** : If the keyword **OR REPLACE** is specified after **CREATE**, the existing view is replaced by a new one without displaying any error message, even when the *view_name* overlaps with the existing view name.

*   *view_name* : Specifies the name of a view to be created. It must be unique in a database.
*   *view_column_definition*

    *   *column_name* : Defines the column of a view.
    *   *column_type* : Specifies the data type of a column.

*   **AS** *select_statement* : A valid **SELECT** statement must be specified. A view is created based on this.

*   **WITH CHECK OPTION** : If this option is specified, the update or insert operation is possible only when the condition specified in the **WHERE** clause of the *select_statement* is satisfied. Therefore, this option is used to disallow the update of a virtual table that violates the condition.

**Example**

.. code-block:: sql

    CREATE TABLE a_tbl(
    id INT NOT NULL,
    phone VARCHAR(10));
    INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333'), (4, NULL), (5, NULL);
     
     
    --creating a new view based on AS select_statement from a_tbl
    CREATE VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL WITH CHECK OPTION;
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
     
.. code-block:: sql

    --WITH CHECK OPTION doesn't allow updating column value which violates WHERE clause
    UPDATE b_view SET phone=NULL;
     
::

    ERROR: Check option exception on view b_view.
     
     
.. code-block:: sql

    --creating view which name is as same as existing view name
    CREATE OR REPLACE VIEW b_view AS SELECT * FROM a_tbl ORDER BY id DESC;
     
    --the existing view has been replaced as a new view by OR REPLACE keyword
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                5  NULL
                4  NULL
                3  '333-3333'
                2  '222-2222'
                1  '111-1111'

Condition for Creating Updatable VIEW
-------------------------------------

A virtual table is updatable if it satisfies the following conditions:

*   The **FROM** clause must include the updatable table or view only.

    In version lower than CUBRID 9.0, only one updatable table can be included to the **FROM** clause it requires. However, two tables in parentheses like FROM (class_x, class_y) can be updated since the two were expressed as one table. In version of CUBRID 9.0 or higher, more than one updatable table is allowed. The **FROM** clause must include only one table or updatable view. However, two tables included in parentheses as in **FROM** (class_x, class_y) can be updated because they represent one table.

*   The **DISTINCT** or **UNIQUE** statement should not be included.
*   The **GROUP BY... HAVING** statement should not be included.
*   Aggregate functions such as **SUM** or **AVG** should not be included.
*   The entire query must consist of queries that can be updated by **UNION ALL**, not by **UNION**. However, the table should exist only in one of the queries that constitute **UNION ALL**.
*   If a record is inserted into a view created by using the **UNION ALL** statement, the system determines into which table the record will be inserted. This cannot be done by the user. To control this, the user must manually insert the row or create a separate view for insertion.

Even when all rules above are satisfied, columns that contains following contents cannot be updated.

*   Path expressions (example: *tbl_name.col_name*)
*   Numeric type column that includes an arithmetic operator

Even though the column defined in the view is updatable, a view can be updated only when an appropriate update authorization is granted on the table included in the **FROM** clause. Also there must be an access authorization to a view. The way to grant an access authorization to a view is the same to grant an access authorization to a table. For details on granting authorization, see :ref:`granting-authorization`.

ALTER VIEW
==========

ADD QUERY Clause
----------------

You can add a new query to a query specification by using the **ADD QUERY** clause of the **ALTER VIEW** statement. 1 is assigned to the query defined when a virtual table was created, and 2 is assigned to the query added by the **ADD QUERY** clause. ::

    ALTER [ VIEW | VCLASS ] view_name
    ADD QUERY select_statement
    [ INHERIT resolution [ {, resolution }_ ] ]
     
    resolution :
    { column_name | method_name } OF superclass_name [ AS alias ]

*   *view_name* : Specifies the name of a view where the query to be added.
*   *select_statement* : Specifies the query to be added.

**Example**

.. code-block:: sql

    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
                4  NULL
                5  NULL
     
.. code-block:: sql
     
    ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
                4  NULL
                5  NULL
                1  '111-1111'
                2  '222-2222'

AS SELECT Clause
----------------

You can change the **SELECT** query defined in the virtual table by using the **AS SELECT** clause in the **ALTER VIEW** statement. This function is working like the **CREATE OR REPLACE** statement. You can also change the query by specifying the query number 1 in the **CHANGE QUERY** clause of the **ALTER VIEW** statement. ::

    ALTER [ VIEW | VCLASS ] view_name AS select_statement
    
*   *view_name* : Specifies the name of a view to be modified.
*   *select_statement* : Specifies the new query statement to replace the **SELECT** statement defined when a view is created.

**Example**

.. code-block:: sql

    ALTER VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL;
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'

CHANGE QUERY Clause
-------------------

You can change the query defined in the query specification by using the **CHANGE QUERY** clause reserved word of the **ALTER VIEW** statement. ::

    ALTER [ VIEW | VCLASS ] view_name
        CHANGE QUERY [ integer ] select_statement [ ; ]

*   *view_name* : Specifies the name of a view to be modified.
*   *integer* : Specifies the number value of the query to be modified. The default value is 1.
*   *select_statement* : Specifies the new query statement to replace the query whose query number is *integer*.

**Example**

.. code-block:: sql

    --adding select_statement which query number is 2 and 3 for each
    ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
    ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id = 3;
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
                4  NULL
                5  NULL
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
     
.. code-block:: sql

    --altering view changing query number 2
    ALTER VIEW b_view CHANGE QUERY 2 SELECT * FROM a_tbl WHERE phone IS NULL;
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
                4  NULL
                5  NULL
                4  NULL
                5  NULL
                3  '333-3333'

DROP QUERY Clause
-----------------

You can drop a query defined in the query specification by using the **DROP QUERY** of the **ALTER VIEW** statement.

**Example**

.. code-block:: sql

    ALTER VIEW b_view DROP QUERY 2,3;
    SELECT * FROM b_view;
     
::

               id  phone
    ===================================
                1  '111-1111'
                2  '222-2222'
                3  '333-3333'
                4  NULL
                5  NULL

DROP VIEW
=========

You can drop a view by using the **DROP VIEW** clause. The way to drop a view is the same as to drop a regular table. ::

    DROP [ VIEW | VCLASS ] view_name [ { ,view_name , ... } ]
    
*   *view_name* : Specifies the name of a view to be dropped.

**Example**

.. code-block:: sql

    DROP VIEW b_view;

RENAME VIEW
===========

You can change the view name by using the **RENAME VIEW** statement. ::

    RENAME [ TABLE |CLASS | VIEW | VCLASS ] old_view_name AS new_view_name [ ; ]

*   *old_view_name* : Specifies the name of a view to be modified.
*   *new_view_name* : Specifies the new name of a view.

**Example**

The following example shows how to rename a view name to *game_2004*.

.. code-block:: sql

    RENAME VIEW game_2004 AS info_2004;